def validate_json_input(json_input: dict) -> None:
    """
    验证JSON输入是否符合SQL查询构建要求
    
    Args:
        json_input: 输入的JSON字典
        
    Raises:
        ValueError: 当输入不符合要求时抛出异常
    """
    if not isinstance(json_input, dict):
        raise ValueError("输入必须是字典类型")

    # 检查table字段
    if 'table' not in json_input:
        raise ValueError("必须包含table字段")

    # 检查select字段
    if 'select' in json_input:
        select_fields = json_input['select']
        if not isinstance(select_fields, list):
            raise ValueError("select字段必须是列表")
        if not all(isinstance(field, str) for field in select_fields):
            raise ValueError("select字段列表中的元素必须是字符串")
    
    # 检查where条件
    if 'where' in json_input:
        where_cond = json_input['where']
        if not (isinstance(where_cond, (dict, list)) or where_cond is None):
            raise ValueError("where条件必须是字典、列表或None")
    
    # 检查limit/page参数
    if 'limit' in json_input and json_input['limit'] is not None:
        if not isinstance(json_input['limit'], int) or json_input['limit'] < 1:
            raise ValueError("limit必须是正整数")
    
    if 'page' in json_input and json_input['page'] is not None:
        if not isinstance(json_input['page'], int) or json_input['page'] < 1:
            raise ValueError("page必须是正整数")
        if 'per_page' in json_input and json_input['per_page'] is not None:
            if not isinstance(json_input['per_page'], int) or json_input['per_page'] < 1:
                raise ValueError("per_page必须是正整数")
    
    # # 检查不能同时指定limit和page
    # if 'limit' in json_input and 'page' in json_input:
    #     if json_input['limit'] is not None and json_input['page'] is not None:
    #         raise ValueError("不能同时指定limit和page参数")

    # 新增order_by字段验证
    if 'order_by' in json_input:
        order_fields = json_input['order_by']
        if not isinstance(order_fields, list):
            raise ValueError("order_by字段必须是列表")
        for field in order_fields:
            if isinstance(field, dict):
                if 'field' not in field or 'order' not in field:
                    raise ValueError("排序字段字典必须包含field和order键")
                if field['order'].upper() not in ('ASC', 'DESC'):
                    raise ValueError("排序方向必须是ASC或DESC")
            elif not isinstance(field, str):
                raise ValueError("order_by列表元素必须是字符串或字典")

def build_sql_query(json_input):
    """
    根据JSON输入构建完整的SQL查询语句
    返回元组 (sql, params)
    """
    validate_json_input(json_input)  # 添加验证

    # 解析TABLE字段
    table_name = json_input.get('table')
    
    # 解析SELECT字段
    select_fields = json_input.get('select', ['*'])
    select_clause = _handle_select(select_fields)
    
    # 解析WHERE条件
    where_cond = json_input.get('where', {})
    where_clause, where_params = _parse_where(where_cond)
    
    # 解析LIMIT和分页
    limit_clause, limit_params = _handle_limit(
        json_input.get('limit'),
        json_input.get('page'),
        json_input.get('per_page')
    )
    
    # 新增解析ORDER BY字段
    order_by_clause = _handle_order_by(json_input.get('order_by'))
    
    # 修改SQL构建部分，添加order_by_clause
    sql_parts = [
        f"SELECT {select_clause} FROM `{table_name}`",
        where_clause,
        order_by_clause,  # 新增位置
        limit_clause
    ]

    # 构建完整SQL语句
    sql = ' '.join([part for part in sql_parts if part])
    
    # 合并 where_params 和 limit_params 列表
    # 确保 where_params 和 limit_params 都是列表
    if not isinstance(where_params, list):
        where_params = [where_params]
    if not isinstance(limit_params, list):
        limit_params = [limit_params]
    all_params = where_params + limit_params
    return sql, all_params

def _handle_select(fields):
    """处理SELECT字段部分"""
    if fields == ['*'] or not fields:
        return '*'
    
    if not isinstance(fields, list):
        raise ValueError("SELECT字段必须是列表")
    
    return ', '.join([f"`{field}`" for field in fields])

def _parse_where(conditions):
    """解析WHERE条件（原有功能升级版）"""
    if not conditions:
        return "", []
    
    where_clause, params = parse_condition(conditions)
    return f"WHERE {where_clause}" if where_clause else "", params

def _handle_limit(limit=None, page=None, per_page=None):
    """处理LIMIT和分页逻辑"""
    params = []
    
    # # 参数冲突检测
    # if limit is not None and page is not None:
    #     raise ValueError("不能同时指定limit和page参数")

    # 如果limit和page都为None，忽略LIMIT
    # 分页处理,
    if page is not None:
        if not isinstance(page, int) or page < 1:
            raise ValueError("页码必须是正整数")
        per_page = per_page or 10
        if not isinstance(per_page, int) or per_page < 1:
            raise ValueError("每页数量必须是正整数")
        
        offset = (page - 1) * per_page
        return "LIMIT %s OFFSET %s", [per_page, offset]
    
    # 普通LIMIT处理
    if limit is not None:
        if not isinstance(limit, int) or limit < 1:
            raise ValueError("LIMIT值必须是正整数")
        return "LIMIT %s", [limit]
    
    return "", []

def parse_condition(cond, params=None):
    if params is None:
        params = []
    
    if isinstance(cond, dict):
        # 处理逻辑操作符 $and/$or
        logical_ops = {'$and', '$or'}
        found_ops = logical_ops.intersection(cond.keys())
        if found_ops:
            if len(found_ops) > 1:
                raise ValueError("不能同时使用多个逻辑操作符")
            op = found_ops.pop()
            sub_conds = cond[op]
            if not isinstance(sub_conds, list):
                raise ValueError(f"逻辑操作符 {op} 需要条件列表")
            
            parts = []
            for sub_cond in sub_conds:
                part_sql, part_params = parse_condition(sub_cond)
                parts.append(f"({part_sql})")  # 为每个子条件添加括号
                params.extend(part_params)
            
            joiner = ' AND ' if op == '$and' else ' OR '
            return f"({joiner.join(parts)})", params
        else:
            # 处理字段条件
            conditions = []
            for field, ops in cond.items():
                field_sql, field_params = parse_field_condition(field, ops)
                conditions.append(field_sql)
                params.extend(field_params)
            
            if len(conditions) > 1:
                return f"({' AND '.join(conditions)})", params
            return conditions[0], params if conditions else ("", params)
    elif isinstance(cond, list):
        # 处理多个条件的AND组合
        parts = []
        for sub_cond in cond:
            part_sql, part_params = parse_condition(sub_cond)
            parts.append(f"({part_sql})")  # 为每个子条件添加括号
            params.extend(part_params)
        
        return f"({' AND '.join(parts)})", params
    else:
        raise ValueError("查询条件必须是字典或列表格式")

def parse_field_condition(field, ops):
    field = f"`{field}`"  # 字段名转义
    if not isinstance(ops, dict):
        # 简写形式，如 "age": 18 转换为等于操作
        return f"{field} = %s", [ops]
    
    clauses = []
    params = []
    for op, value in ops.items():
        clause, op_params = handle_operator(field, op, value)
        clauses.append(clause)
        params.extend(op_params)
    
    if len(clauses) > 1:
        return f"({' AND '.join(clauses)})", params
    return clauses[0], params if clauses else ("", params)

def handle_operator(field, op, value):
    if op == '$eq':
        return f"{field} = %s", [value]
    elif op == '$ne':
        return f"{field} != %s", [value]
    elif op == '$gt':
        return f"{field} > %s", [value]
    elif op == '$gte':
        return f"{field} >= %s", [value]
    elif op == '$lt':
        return f"{field} < %s", [value]
    elif op == '$lte':
        return f"{field} <= %s", [value]
    elif op == '$like':
        return f"{field} LIKE %s", [value]
    elif op == '$in':
        if not isinstance(value, list):
            raise ValueError("$in 操作需要列表值")
        placeholders = ', '.join(['%s']*len(value))
        return f"{field} IN ({placeholders})", value
    elif op == '$between':
        if not isinstance(value, list) or len(value) != 2:
            raise ValueError("$between 需要两个元素的列表")
        return f"{field} BETWEEN %s AND %s", value
    elif op == '$exists':
        if not isinstance(value, bool):
            raise ValueError("$exists 需要布尔值")
        return f"{field} IS NOT NULL" if value else f"{field} IS NULL", []
    else:
        raise ValueError(f"不支持的操作符: {op}")

def format_sql_with_params(sql: str, params: list) -> str:
    """
    将SQL语句和参数拼接成可直接执行的SQL字符串
    
    Args:
        sql: 包含占位符的SQL语句
        params: 参数列表
        
    Returns:
        拼接后的完整SQL字符串
    """
    if not params:
        return sql
    
    # 转义参数中的特殊字符
    escaped_params = []
    for param in params:
        if param is None:
            escaped_params.append("NULL")
        elif isinstance(param, str):
            # 第一步：转义单引号和反斜杠
            escaped_param = param.replace("\\", "\\\\").replace("'", "''")
            # 第二步：包裹单引号并添加
            escaped_params.append(f"'{escaped_param}'")
        elif isinstance(param, bool):
            escaped_params.append("1" if param else "0")
        else:
            escaped_params.append(str(param))
    
    try:
        return sql % tuple(escaped_params)
    except Exception as e:
        raise ValueError(f"SQL参数替换失败: {str(e)}")

# 增强版示例用法
if __name__ == "__main__":
    test_cases = [
        {
            "input": {"select": ["name", "email"], "table": "users", "where": {"age": {"$gte": 18}}},
            "output": ("SELECT `name`, `email` FROM `users` WHERE `age` >= %s", [18])
        },
        {
            "input": {
                "where": 
                    {"$and": [
                        {"name": {"$like": "John%"}},
                        {"email": {"$exists": True, "$like": "@163.com"}},
                        {"age": {"$lte": 30}}
                    ]},
                "table": "customers",
                "page": 3,
                "per_page": 20
            },
            "output": ("SELECT * FROM `customers` WHERE ((`name` LIKE %s) OR (`email` IS NOT NULL)) LIMIT %s OFFSET %s", 
                      ["John%", 20, 20])
        },
        {
            "input": {"select": ["product"], "table": "products", "limit": 5},
            "output": ("SELECT `product` FROM `products` LIMIT %s", [5])
        }
    ]

    for idx, case in enumerate(test_cases, 1):
        sql, params = ('',[])
        try:
            sql, params = build_sql_query(case["input"])
            assert (sql, params) == case["output"]
            print(f"测试用例 {idx} 通过")
        except Exception as e:
            print(f"测试用例 {idx} 失败: {str(e)}")
            print(f"预期: {case['output']}")
            print(f"生成: {sql} | {params}")
        finally:
            print(f"SQL语句: ", format_sql_with_params(sql, params))

    # 新增测试用例
    test_sql = "SELECT * FROM users WHERE name = %s AND age > %s"
    test_params = ["John O'Connor", 25]
    print("\nSQL拼接测试:")
    print("原始SQL:", test_sql)
    print("参数:", test_params)
    print("拼接结果:", format_sql_with_params(test_sql, test_params))



# 新增排序处理函数
def _handle_order_by(fields):
    """处理ORDER BY字段"""
    if not fields:
        return ""
    
    order_clauses = []
    for field in fields:
        if isinstance(field, dict):
            clause = f"`{field['field']}` {field['order'].upper()}"
        else:
            clause = f"`{field}`"
        order_clauses.append(clause)
    
    return f"ORDER BY {', '.join(order_clauses)}" if order_clauses else ""


